
import configparser
import datetime
import logging
from app import app
import pyodbc

from utils.configReader import myconfigReader
from utils.dbm import DBManager


class MSSqlManager(object):

    def __init__(self):
        self.logger = logging.getLogger(__name__)
        self.host = myconfigReader.mssql_host
        self.port = myconfigReader.mssql_port
        self.schema = myconfigReader.mssql_schema
        self.username = myconfigReader.mssql_username
        self.password = myconfigReader.mssql_password

        # WINDOWS
        param = r'DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' \
                % (self.host, self.schema, self.username, self.password)
        self.conn = pyodbc.connect(param)

        # # MAC
        # self.param = 'DSN=MYMSSQL;UID=' + self.username + ';PWD=' + self.password
        # self.conn = pyodbc.connect(self.param)

    def initConn(self):
        pass

    def selectShouhou(self):
        self.initConn()
        sql = "select id, 接收人, 发起人, 手机 from JiaJuDB.dbo.YW_PGD where 推送 = '未推送'"
        self.crsr = self.conn.cursor()
        rows = self.crsr.execute(sql)
        return rows

    def updateShouhou(self):
        sql = "update JiaJuDB.dbo.YW_PGD set 推送 = '已推送'";
        self.crsr = self.conn.cursor()
        self.crsr.execute(sql)
        self.conn.commit()

    def selectAZ(self):
        self.initConn()
        sql = "select 序号, 顾客姓名, 电话, 手机 from JiaJuDB.dbo.YW_XS where tuisong = 0"
        self.crsr = self.conn.cursor()
        rows = self.crsr.execute(sql)
        return rows

    def selectJD(self):
        self.initConn()
        sql = "select 序号, 顾客姓名, 电话, 手机 from JiaJuDB.dbo.YW_XS where jindu = '2'"
        self.crsr = self.conn.cursor()
        rows = self.crsr.execute(sql)
        return rows

    # 检查意向客户创建的推送
    def selectYXKH(self):
        self.initConn()
        sql = "select 序号, 所属店面, 建档人员 from JiaJuDB.dbo.YX_KHGL where 推送 != '是'"
        self.crsr = self.conn.cursor()
        rows = self.crsr.execute(sql)
        return rows

    def updateYXKH(self):
        self.initConn()
        sql = "update JiaJuDB.dbo.YX_KHGL set 推送 = '是' where 推送 != '是'"
        self.crsr = self.conn.cursor()
        self.crsr.execute(sql)
        self.conn.commit()

    def updateAZ(self):
        self.initConn()
        sql = "update JiaJuDB.dbo.YW_XS set tuisong = 1 where tuisong = 0"
        self.crsr = self.conn.cursor()
        self.crsr.execute(sql)
        self.conn.commit()


    # jindu等于2，表示还没推送，=3表示已经推送过
    def updateJD(self):
        self.initConn()
        sql = "update JiaJuDB.dbo.YW_XS set jindu = '3' where jindu = '2'"
        self.crsr = self.conn.cursor()
        self.crsr.execute(sql)
        self.conn.commit()

    def close(self):
        self.crsr.close()
        self.conn.close()

if __name__ == '__main__':
    ms = MSSqlManager()

    rows = ms.selectYXKH()
    for row in rows:
        print(row)
    ms.updateYXKH()


